from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# orm:object relation mapping
# 创建 SQLite 内存数据库引擎
mysql_connection_string = 'mysql+mysqlconnector://root:admin@localhost:3306/orm_coding'
engine = create_engine(mysql_connection_string)

# 创建映射类的基类
Base = declarative_base()


# 用户类，映射到 users 表
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String, nullable=False, unique=True)
    password = Column(String, nullable=False)

    # 建立与权限表的多对多关系
    permissions = relationship('Permission', secondary='user_permissions', back_populates='users')


# 权限类，映射到 permissions 表
class Permission(Base):
    __tablename__ = 'permissions'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)

    # 建立与用户表的多对多关系
    users = relationship('User', secondary='user_permissions', back_populates='permissions')


# 关联表类，用于实现多对多关系
class UserPermission(Base):
    __tablename__ = 'user_permissions'

    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    permission_id = Column(Integer, ForeignKey('permissions.id'), primary_key=True)


# 使用 Base.metadata.create_all 方法创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 添加测试数据
user1 = User(username='John', password='password123')
user2 = User(username='Alice', password='securepass')

permission1 = Permission(name='READ')
permission2 = Permission(name='WRITE')

user1.permissions.extend([permission1, permission2])
user2.permissions.append(permission1)

session.add_all([user1, user2, permission1, permission2])
session.commit()


# 查询用户及其权限
users = session.query(User).all()
for user in users:
    print(f"User: {user.username}, Permissions: {[p.name for p in user.permissions]}")
